/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Laporan;

import Entity.DatabaseConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author wienda
 */
public class ControllerLaporan {

    private static Connection conn = DatabaseConnection.getConnection();
  

    public ControllerLaporan() {
    }

    public ControllerLaporan(Connection konn) {
        ControllerLaporan.conn = konn;
    }

    public static Object[][] getAllDataLaporan() throws SQLException {
        PreparedStatement pt = null;
        ResultSet rs = null;
        String sql = "select peminjaman.idmember, member.nama, peminjaman.idbuku, buku.judulbuku, tanggal, "
                + "round((sysdate-tanggal)) as lamabawa, round((sysdate-tanggal)-3) as keterlambatan,round((sysdate-tanggal)-3)* 3000 as denda from peminjaman,member, buku "
                + "where(sysdate-tanggal) > 3 and peminjaman.idmember = member.idmember and peminjaman.idbuku = buku.idbuku and tanggalkembali is null";
        pt = conn.prepareStatement(sql);
        rs = pt.executeQuery();

        List<Object[]> kategoris = new ArrayList<Object[]>();
        while (rs.next()) {
            Object[] ob = new Object[8];
            ob[0] = rs.getString(1);
            ob[1] = rs.getString(2);
            ob[2] = rs.getString(3);
            ob[3] = rs.getString(4);
            ob[4] = rs.getDate(5);
            ob[5] = rs.getInt(6);
            ob[6] = rs.getInt(7);
            ob[7] = rs.getInt(8);
            kategoris.add(ob);
        }
        conn.commit();
        Object[][] obj = new Object[kategoris.size()][8];
        for (int i = 0; i < kategoris.size(); i++) {
            obj[i] = kategoris.get(i);
        }
        return obj;

    }
}
